package com.crimax.skor.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import com.crimax.skor.model.RgbObject;
import com.crimax.skor.model.SearchObject;
import com.crimax.skor.model.SkorObject;
import com.crimax.skor.utility.SkorUtility;

public class DbUtility {

	private static int COLOR_RANGE =10; 
	
	
	private String dbms = "mysql";
	private String serverName = "x1202.space2u.com";
	private String portNumber = "3306";
	private String userName = "klackskor";
	private String password = "BAKved88";

	public Connection getConnection() throws SQLException, InstantiationException, IllegalAccessException,
			ClassNotFoundException {

		Connection conn = null;
		Properties connectionProps = new Properties();
		connectionProps.put("user", this.userName);
		connectionProps.put("password", this.password);

		Class.forName("com.mysql.jdbc.Driver").newInstance();
		conn = DriverManager.getConnection("jdbc:" + this.dbms + "://" + this.serverName + ":" + this.portNumber
				+ "/skor", this.userName, this.password);

		System.out.println("Connected to database");
		return conn;
	}
	
	public List<SkorObject> searchSkorByColor (RgbObject color,SearchObject searchObject, Connection con) throws SQLException{
		
		SkorUtility skorUtility = new SkorUtility();
		String query = "";
		
		if(color!=null){
			double[] rgbToLab = skorUtility.rgbToLab(new Integer(color.getRed()),new Integer(color.getGreen()), new Integer(color.getBlue()));
			
			String whereColor = this.getWhereClauseColorInterval(rgbToLab);
	    	String whereFilter = this.getWhereClauseFilter(searchObject);
			
	    	
	    	if(whereFilter==null || whereFilter.isEmpty()){
	    		query ="SELECT * from products where"+whereColor;
	    	}else{
	    		query ="SELECT * from products where"+whereColor+" and ("+whereFilter+")"; 
	    	}
		}else{
			String whereFilter = this.getWhereClauseFilter(searchObject);
			if(whereFilter==null || whereFilter.isEmpty()){
				query ="SELECT * from products";
			}else{
				query ="SELECT * from products where "+whereFilter;
			}
			
		}
		
    	System.out.println(query);
    	
    	
    	Statement stmt = con.createStatement();
    	ResultSet rs = stmt.executeQuery(query);
    	
    	List<SkorObject> skorList = this.mapProductAndClose(rs, stmt);
		
		return skorList;
	}
	
	
	public List<SkorObject> getAll ( Connection con) throws SQLException{
		
		
		Statement stmt = con.createStatement();
    	
    	String query ="SELECT * from products"; 
    	System.out.println(query);
		
    	ResultSet rs = stmt.executeQuery(query);
    	
    	List<SkorObject> skorList = this.mapProductAndClose(rs, stmt);
		
		return skorList;
	}
	
	private List<SkorObject> mapProductAndClose(ResultSet rs, Statement stm) throws SQLException{
		
		ArrayList<SkorObject> prods = new ArrayList<SkorObject>();

		while (rs.next()) {
			SkorObject prod = new SkorObject();
			
			prod.setImgURL(rs.getString("graphicUrl"));
			prod.setName(rs.getString("supplier"));
			prod.setDesc(rs.getString("description"));
			prod.setUrl(rs.getString("productUrl"));
			prod.setId(rs.getString("id"));
			prod.setPrice(rs.getString("price"));
			prods.add(prod);
		}
//		   ID int PRIMARY KEY NOT NULL,
//		   SKU varchar(255) NOT NULL,
//		   EAN varchar(255),
//		   description longtext,
//		   price int,
//		   currency varchar(255),
//		   productUrl longtext,
//		   graphicUrl longtext,
//		   inStock varchar(255),
//		   supplier varchar(255),
//		   name varchar(255),
//		   labl double,
//		   laba double,
//		   labb double
		
		System.out.println("return products "+prods.size());
		return prods;
	}
	
	private String getWhereClauseColorInterval(double[] rgbToLab){
		
		int value = 10;
		
		int lMax=(int) (rgbToLab[0]+value);
		int lMin=(int) (rgbToLab[0]-value);
		int aMax=(int) (rgbToLab[1]+value);
		int aMin=(int) (rgbToLab[1]-value);
		int bMax=(int) (rgbToLab[2]+value);
		int bMin=(int) (rgbToLab[2]-value);
		
		StringBuilder builder = new StringBuilder();
		builder.append(" labl>");
		builder.append(lMin);
		builder.append(" and labl<");
		builder.append(lMax);
		builder.append(" and laba<");
		builder.append(aMax);
		builder.append(" and laba>");
		builder.append(aMin);
		builder.append(" and labb>");
		builder.append(bMin);
		builder.append(" and labb<");
		builder.append(bMax);
	
		
		return builder.toString();
	}
	
private String getWhereClauseFilter(SearchObject obj){
		
		StringBuilder builder = new StringBuilder();
		String and ="";
		
		if(obj.getHogklackatParam()){
			and=" or ";
			builder.append("hogklackade=true");
		}
		
		if(obj.getPumpsParam()){
			builder.append(and+"pump=true");
			and=" or ";
		}
		
		if(obj.getSandalerParam()){
			builder.append(and+"sandaler=true");
			and=" or ";
		}
		
		if(obj.getStovlarParam()){
			builder.append(and+"stovlar=true");
			and=" or ";
		}
		if(obj.getPartyParam()){
			builder.append(and+"party=true");
			and=" or ";
		}		
		
		if(obj.getMaxParam()==-1){
			
		}else{
			if(and.isEmpty()){
				builder.append("price<"+obj.getMaxParam());
			}else{
				builder.append(" and price<"+obj.getMaxParam());
			}
			and=" and ";
		}
		if(obj.getMinParam()==-1){
			
		}else{
			if(and.isEmpty()){
				builder.append("price>"+obj.getMinParam());
			}else{
				builder.append(" and price>"+obj.getMinParam());
			}
		}
		
		return builder.toString();
	}
	
	private List<SkorObject> orderByProminentColor(){
		
		
		return null;
	}
	
}
